const mysql = require('mysql')
const moment = require('moment')
// 加密模块中的随机生成数
const { randomInt } = require('crypto')
const sqlconfig = require('./sqlconfig')

// 方式1   使用mysql.createConnection连接数据库
/* let mySql = (req,res,sql)=>{
  // console.log(sql);
    //连接数据库
    let conn= mysql.createConnection(sqlconfig)
    // 执行SQL语句
    conn.query(sql, (err, result)=>{
      // 错误信息
      if(err){
        console.log(err);
        return
      }
      console.log(result);
      res.send({
        message: '操作成功!',
        result
      })
    })
} */

// 方式2   使用连接池 pool.createPool()
let pool = mysql.createPool(sqlconfig)
// 执行数据库
const exec2 = (sql, values) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, conn) => {
      if (err) {
        //连接错误
        reject(err)
      } else {
        //连接成功
        // sql语句中使用?占位符, values传递是个数组
        conn.query(sql, values, (err, data) => {
          if (err) {
            //操作失败
            reject(err)
          } else {
            resolve({
              code: 0,
              message: '操作成功!',
              data,
            })
            // resolve(data)
          }
        })
      }
      // 当连接不再使用时，用conn对象的release方法将其归还到连接池中
      conn.release()
    })
  })
}
const exec = (sql) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, conn) => {
      if (err) {
        //连接错误
        reject(err)
      } else {
        console.log('链接成功')
        //连接成功
        conn.query(sql, (err, data) => {
          if (err) {
            //操作失败
            reject(err)
          } else {
            resolve({
              code: 0,
              message: '操作成功!',
              data,
            })
            // resolve(data)
          }
        })
      }
      // 当连接不再使用时，用conn对象的release方法将其归还到连接池中
      conn.release()
    })
  })
}

// 查询
const searchList = (req, res, table = 'books') => {
  // 根据输入的查询条件查找数据   若无参数则查询所有
  let sql = `select * from  ${table} where 1=1`
  let keys = Object.keys(req.query)
  let values = Object.values(req.query)
  let keyArr = []
  let valArr = []
  let str = ''
  let i = 0
  keys.forEach((item, index) => {
    keyArr.push(item.trim())
    console.log(item.trim());
    valArr.push(`${values[index].trim()}`)
    str += ` and ${keyArr[i]} like '%${valArr[i]}%' `
    i++
  })
  sql += str
  exec(sql).then(result => {
    res.send({
      code: 1,
      message: "查询成功!",
      result
    })
  })
}

// 增加数据
// 封装对象的方法
const addObj = (obj, table) => {
  if (table === 'books') {
    let { isbn = '', bookname, imgUrl = '', publisher = '', pubYear = null, pages = 0, content = '', price = 0.0 } = obj;
    if (pubYear !== null) {
      pubYear = moment(pubYear, 'YYYY-MM-DD').toDate();
    }
    // 随机生成 bid   randomInt(x)返回0~x的随机整数
    const bid = moment().get() + '_' + (randomInt(9999999) + 1) + '';
    return Array.of(bid, isbn, bookname, imgUrl, publisher, pubYear, pages, content, price);
  }
  if (table === 'users') {
    let { nickname = '', password, phone, email, uImg = null } = obj;
    // 随机生成 uid
    const uid = moment().get() + '_' + (randomInt(9999999) + 1) + '';
    return Array.of(uid, nickname, password, phone, email, uImg);
  }
}

const addList = (req, res, table = 'books') => {
  let { data } = req.body
  let values = []
  // data是个数组
  if (Array.isArray(data)) {
    values = data.map(obj => addObj(obj, table))
  } else {
    // 对象
    values = addObj(data, table);
  }
  let sql = ''
  if (table === 'books') {
    // sql语句中 ? 为占位符, 表该处有内容, 具体是什么,后边传值,   所传数据(数组中的元素)的个数必须要和占位符的个数一样, 要以数组的形式进行传递(不论有几个元素)
    sql = ` insert into books(bid,isbn,bookname,imgUrl,publisher,pubYear,pages,content,price) values (?) `
  }
  if (table === 'users') {
    sql = ` insert into users(uid,nickname,password,phone,email,uImg) values (?) `
  }
  console.log("values:", values);
  // [values] 因为values本身是个数组,如果直接传递, 可能会有多个元素,与?个数不对应,     [values]相当于定义数组, 故传递时,变成了二维数组
  exec2(sql, [values]).then(data => {
    res.send({
      code: 1,
      message: "添加成功!",
      data
    });
  });
}

// 更新数据  根据id更新
const updateList = (req, res, id, table = 'books', str = 'bid') => {
  const keys = Object.keys(req.body)
  const val = Object.values(req.body)
  let sql = ''
  let items = []
  keys.forEach((item, index) => {
    items.push(`${item} = '${val[index]}'`)
  })
  sql = `update ${table} set  ${items} where ${str} = '${id}'`
  console.log(sql)
  // mySql(req, res, sql)
  exec(sql).then(result => {
    res.send({
      code: 1,
      result
    })
  })
}

// 删除
const deleteList = (req, res, table = 'books') => {
  const keys = Object.keys(req.body)
  const val = Object.values(req.body)
  console.log(keys);
  if (keys.length !== 0) {
    // 按理来说, 需要查询数据库中是否有该数据
    let sql = `delete from  ${table}  where 1=1`
    let str = ''
    keys.forEach((item, index) => {
      str += ` and ${item} like '%${val[index]}%' `
    })

    console.log(sql);
    // mySql(req, res, sql)
    // let searchSQL= `select * from  ${table} where 1=1 ${str}`
    // exec(searchSQL).then(result=>{
    // })

    sql += str
    exec(sql).then(result => {
      res.send({
        code: 1,
        message: "删除成功!",
        result
      })
    })

  } else {
    res.send({
      message: '删除条件为空!',
    })
  }
}

const login = (req, res) => {
  // 根据 phone / email 作为账号来登录 输入内容不为空， 判断是Email还是phone   根据对应信息查询数据， 如果能查到则登录成功， 否则登陆失败                                           
  let paramsArr = Object.values(req.body)
  // 有数据
  let flag = 0
  if (paramsArr && paramsArr.length > 0) {
    // 遍历数组，并去空
    paramsArr.forEach(item => {
      if (item.trim().length === 0) {
        flag++
      }
    })
    // 账号 密码存在
    if (flag === 0 && req.body.password.length > 0) {
      let username = paramsArr.toString().indexOf('@') > 0 ? 'email' : 'phone'
      let sql = `select * from users where `
      if (username === 'email') {
        const { password, email } = req.body
        // 邮箱登录
        sql += ` password = '${password}' and email = '${email}' `
        console.log(sql);
        // 查询数据， 查到则登录成功
        exec(sql).then(result => {
          console.log(result.data);
          if (result.data.length > 0) {
            res.send({
              code: 1,
              message: '登录成功!',
              result
            })
          } else {
            res.send({
              code: 0,
              message: '登录失败!'
            })
          }
        })
      }
      if (username === 'phone') {
        const { password, phone } = req.body
        // 邮箱登录
        sql += ` password = '${password}' and phone = '${phone}' `
        console.log(sql);
        // 查询数据， 查到则登录成功
        exec(sql).then(result => {
          // console.log(result);
          if (result.data.length > 0) {
            res.send({
              code: 1,
              message: '登录成功!',
              result
            })
          } else {
            res.send({
              code: 0,
              message: '登录失败!'
            })
          }
        })
      }
    } else {
      // 账号或密码为空
      res.send({
        code: -1,
        message: "账号或密码不能为空!"
      })
    }

  } else {
    res.send({
      code: -1,
      message: "账号和密码不能为空!"
    })
  }
}

module.exports = {
  searchList,
  addList,
  updateList,
  deleteList,
  login,
}